package com.shujia.day18;

import com.mysql.jdbc.Driver;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

public class Demo08ReadData2Mysql {
    public static void main(String[] args) throws SQLException, IOException {

        /*
            TODO 从本地读取数据写入到Mysql数据库中
                    Demo07中处理数据相对较慢，原因为每1条数据都会执行一次SQL语句，SQl启动较慢
                        于是，尝试在一个SQL语句中添加多条数据
                         insert into txt2mysqlscore (stu_id,sub_id,score) values (值，值，值),(值，值，值);
         */
        long startTime = System.currentTimeMillis();
        DriverManager.registerDriver(new Driver());
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/bigdata_30?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai", "root", "123456");
        Statement statement = connection.createStatement();

        for (int i = 0; i < 20; i++) {
            readData2Mysql(statement,"insert into txt2mysqlscore (stu_id,sub_id,score) values ");
        }
        statement.close();
        connection.close();
        long endTime = System.currentTimeMillis();
        System.out.println("当前程序执行时间为："+(endTime-startTime)/1000.0+"秒"); // 当前程序执行时间为：2.572秒
    }

    public static void readData2Mysql(Statement statement,String initSql) throws IOException, SQLException {
        // TODO 1.对SQL语句的基础语法定义
        String sql = initSql;
        BufferedReader bufferedReader = new BufferedReader(new FileReader("data/score.txt"));
        // TODO 2.每读取到一行数据，对SQL进行拼接
        String readLine = null;
        int num = 0;
        while ((readLine = bufferedReader.readLine()) != null) {
            String[] columns = readLine.split(",");
            if (columns.length == 3) {
                sql += "('" + columns[0] + "','" + columns[1] + "','" + Integer.valueOf(columns[2]) + "'),";
                num += 1;
                // TODO 3.设定当100条数据时，执行一次SQL语句
                if (num >= 100) {
                    // SQL中最后一个逗号要进行去除
                    sql = sql.substring(0,sql.length()-1);
                    statement.executeUpdate(sql);
                    // 当执行后，SQL及num要进行初始化，重写开始
                    sql = initSql ;
                    num = 0;
                }
            }
        }

    }
}
